#!/bin/bash
#description: Use mysqldump to backup mysql.
# GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'
#定义变量
USER="backup"
PASS="Aa123321"
HOST="localhost"
DATE="$(date +%F)"
MYCMD="/usr/local/mysql/bin/mysql -u$USER -p$PASS"
BACKUPDIR="/opt/backup/database/"
MYDUMP="/usr/local/mysql/bin/mysqldump -u$USER -p$PASS  -B -R -E --triggers  --single-transaction --master-data=2"
DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|test"`
#判断备份目录是否存在
[ ! -d ${BACKUPDIR}/${DATE} ] && mkdir -p ${BACKUPDIR}/${DATE}
#刷新生成二进制日志
$MYCMD -e "flush logs;"
#循环备份数据库
for dbname in $DBLIST;do
   $MYDUMP $dbname|gzip >${BACKUPDIR}/${DATE}/${dbname}.sql.gz 
done
#删除7天前的文件
cd $BACKUPDIR && find . -type d -mtime +7 |xargs rm -f  

